/*
 * Copyright 2003-2005 ExcelUtils http://excelutils.sourceforge.net
 * Created on 2005-7-5
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *      http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package com.haohope.excel;

import java.io.InputStream;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletContext;
import org.apache.commons.beanutils.DynaBean;
import org.apache.commons.beanutils.LazyDynaBean;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
//import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.util.CellRangeAddress;

import com.haohope.excel.exceptions.ExcelException;


/**
 * <p>
 * <b>ExcelUtils</b> is a class which parse the excel report template
 * </p>
 * 
 * @author rainsoft
 * @version $Revision: 1.11 $ $Date: 2005/11/02 10:31:22 $
 */
@SuppressWarnings("deprecation")
public class ExcelUtil {
  @SuppressWarnings("rawtypes")
  static ThreadLocal context = new ThreadLocal();
  public static HSSFWorkbook createHSSFWorkbook(ServletContext ctx, String config, Object context) {
	  try {
	      HSSFWorkbook wb = WorkbookUtil.openWorkbook(ctx, config);
	      parseWorkbook(context, wb);
	      return wb;
	  } catch (Exception e) {
	      throw new ExcelException(e.getMessage());
	  }
  }
  /**
   * parse the excel template and output excel to outputStream.
   * 
   * @param ctx ServletContext
   * @param config Excel Template Name
   * @param context All Data
   * @param out OutputStream
   * @throws ExcelException
   */
  public static void export(ServletContext ctx, String config, Object context, OutputStream out) throws ExcelException {
    try {
    	HSSFWorkbook wb = createHSSFWorkbook(ctx, config,context);
      /*HSSFWorkbook wb = WorkbookUtil.openWorkbook(ctx, config);
      parseWorkbook(context, wb);*/
      wb.write(out);
    } catch (Exception e) {
      throw new ExcelException(e.getMessage());
    }
  }
  public static HSSFWorkbook createHSSFWorkbook(ServletContext ctx, String config, int sheetIndex, Object context) {
	  try {
	      HSSFWorkbook wb = WorkbookUtil.openWorkbook(ctx, config);
	      parseWorkbook(context, wb,sheetIndex);
	      return wb;
	  } catch (Exception e) {
	      throw new ExcelException(e.getMessage());
	  }
  }
  /**
   * parse the excel template in a sheet and output excel to outputStream.
   * 
   * @param ctx
   * @param config
   * @param sheetIndex
   * @param context
   * @param out
   * @throws ExcelException
   */
  public static void export(ServletContext ctx, String config, int sheetIndex, Object context, OutputStream out)
      throws ExcelException {
    try {
    	HSSFWorkbook wb = createHSSFWorkbook(ctx, config,sheetIndex,context);
      /*HSSFWorkbook wb = WorkbookUtil.openWorkbook(ctx, config);
      parseWorkbook(context, wb, sheetIndex);*/
      wb.write(out);
    } catch (Exception e) {
      throw new ExcelException(e.getMessage());
    }
  }

  /**
   * parse the excel template and output excel to outputStream in default
   * context.
   * 
   * @param ctx
   * @param config
   * @param out
   * @throws ExcelException
   */
  public static void export(ServletContext ctx, String config, OutputStream out) throws ExcelException {
    try {
      export(ctx, config, getContext(), out);
    } catch (Exception e) {
      throw new ExcelException(e.getMessage());
    }
  }
  /** 
   * 遍历树创建表头 
   * 规则： 1、 以二维数组的形式保存树节点          1:表头名称  2：表头索引 3：表头层级   4：父表头索引 
   *     2、表头存放的顺序必须按照空间顺序从左到右存储   
   * @param header 
   * @param sheet 
   * @param index 指示表头从第几行开始创建 
   */  

///**
	// * 把一个excel中的cellstyletable复制到另一个excel，这里会报错，不能用这种方法，不明白呀？？？？？
	// * @param fromBook
	// * @param toBook
	// */
	// public static void copyBookCellStyle(HSSFWorkbook fromBook,HSSFWorkbook
	// toBook){
	// for(short i=0;i<fromBook.getNumCellStyles();i++){
	// HSSFCellStyle fromStyle=fromBook.getCellStyleAt(i);
	// HSSFCellStyle toStyle=toBook.getCellStyleAt(i);
	// if(toStyle==null){
	// toStyle=toBook.createCellStyle();
	// }
	// copyCellStyle(fromStyle,toStyle);
	// }
	// }
	/**
	 * 复制一个单元格样式到目的单元格样式
	 * 
	 * @param fromStyle
	 * @param toStyle
	 */
	public static void copyCellStyle(HSSFWorkbook wb, HSSFCellStyle fromStyle,
			HSSFCellStyle toStyle) {
		toStyle.setAlignment(fromStyle.getAlignmentEnum());
		// 边框和边框颜色
		toStyle.setBorderBottom(fromStyle.getBorderBottomEnum());
		toStyle.setBorderLeft(fromStyle.getBorderLeftEnum());
		toStyle.setBorderRight(fromStyle.getBorderRightEnum());
		toStyle.setBorderTop(fromStyle.getBorderTopEnum());
		toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
		toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
		toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
		toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());

		// 背景和前景
		toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
		toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());

		toStyle.setDataFormat(fromStyle.getDataFormat());
		toStyle.setFillPattern(fromStyle.getFillPatternEnum());
		toStyle.setFont(fromStyle.getFont(wb));
		toStyle.setHidden(fromStyle.getHidden());
		toStyle.setIndention(fromStyle.getIndention());// 首行缩进
		toStyle.setLocked(fromStyle.getLocked());
		toStyle.setRotation(fromStyle.getRotation());// 旋转
		toStyle.setVerticalAlignment(fromStyle.getVerticalAlignmentEnum());
		toStyle.setWrapText(fromStyle.getWrapText());

	}

	/**
	 * Sheet复制
	 * 
	 * @param fromSheet
	 * @param toSheet
	 * @param copyValueFlag
	 */
	public static void copySheet(HSSFWorkbook wb, HSSFSheet fromSheet,
			HSSFSheet toSheet, boolean copyValueFlag) {
		// 合并区域处理
		mergerRegion(fromSheet, toSheet);
		Boolean flag = false;
		for (Iterator<?> rowIt = fromSheet.rowIterator(); rowIt.hasNext();) {
			HSSFRow tmpRow = (HSSFRow) rowIt.next();
			HSSFRow newRow = toSheet.createRow(tmpRow.getRowNum());
			// 行复制
			copyRow(wb, tmpRow, newRow, copyValueFlag);
			// 复制高度
			newRow.setHeight(tmpRow.getHeight());
			if (!flag) {
				flag = true;
				for (int i = tmpRow.getFirstCellNum(); i <= tmpRow
						.getLastCellNum(); i++) {
					toSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
				}
			}
		}
	}

	/**
	 * 行复制功能
	 * 
	 * @param fromRow
	 * @param toRow
	 */
	public static void copyRow(HSSFWorkbook wb, HSSFRow fromRow, HSSFRow toRow,
			boolean copyValueFlag) {
		for (Iterator<?> cellIt = fromRow.cellIterator(); cellIt.hasNext();) {
			HSSFCell tmpCell = (HSSFCell) cellIt.next();
			//HSSFCell newCell = toRow.createCell(tmpCell.getCellNum());
			HSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex());
			copyCell(wb, tmpCell, newCell, copyValueFlag);
		}
	}
	/**
	 * 复制原有sheet的合并单元格到新创建的sheet
	 */
	public static void mergerRegion(HSSFSheet fromSheet, HSSFSheet toSheet) {
		int sheetMergerCount = fromSheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergerCount; i++) {
			//Region mergedRegionAt = fromSheet.getMergedRegionAt(i);
			CellRangeAddress mergedRegionAt = fromSheet.getMergedRegion(i);
			toSheet.addMergedRegion(mergedRegionAt);
		}
	}
	/**
	 * 复制单元格
	 */
	public static void copyCell(HSSFWorkbook wb, HSSFCell srcCell,
			HSSFCell distCell, boolean copyValueFlag) {
		//copyCellStyle(wb, srcCell.getCellStyle(), newstyle);
		// distCell.setEncoding(srcCell.getEncoding());
		// 样式
		//distCell.setCellStyle(newstyle);
		//复制单元格样式
		try {
			distCell.setCellStyle(srcCell.getCellStyle());
		}catch(Exception e) {
			distCell.getCellStyle().cloneStyleFrom(srcCell.getCellStyle());
		}
		
		// 评论
		if (srcCell.getCellComment() != null) {
			distCell.setCellComment(srcCell.getCellComment());
		}
		// 不同数据类型处理
		int srcCellType = srcCell.getCellType();
		distCell.setCellType(srcCellType);
		if (copyValueFlag) {
			if (srcCellType == HSSFCell.CELL_TYPE_NUMERIC) {
				if (HSSFDateUtil.isCellDateFormatted(srcCell)) {
					distCell.setCellValue(srcCell.getDateCellValue());
				} else {
					distCell.setCellValue(srcCell.getNumericCellValue());
				}
			} else if (srcCellType == HSSFCell.CELL_TYPE_STRING) {
				distCell.setCellValue(srcCell.getRichStringCellValue());
			} else if (srcCellType == HSSFCell.CELL_TYPE_BLANK) {
				// nothing21
			} else if (srcCellType == HSSFCell.CELL_TYPE_BOOLEAN) {
				distCell.setCellValue(srcCell.getBooleanCellValue());
			} else if (srcCellType == HSSFCell.CELL_TYPE_ERROR) {
				distCell.setCellErrorValue(srcCell.getErrorCellValue());
			} else if (srcCellType == HSSFCell.CELL_TYPE_FORMULA) {
				distCell.setCellFormula(srcCell.getCellFormula());
			} else { // nothing29
			}
		}
	}
	@SuppressWarnings("unchecked")
	public static HSSFWorkbook createHSSFWorkbook(ServletContext ctx, String config,
			ExcelSheet sheet, int sheetindex) {
		try {
			HSSFWorkbook wb = WorkbookUtil.openWorkbook(ctx, config);
			HSSFSheet fromSheet = wb.getSheetAt(sheetindex);
			String fromname = fromSheet.getSheetName();
			while (wb.getNumberOfSheets() > 0) {
				wb.removeSheetAt(0);
			}
			ExcelUtil.context.set(null);
			addValue("value", sheet.getData());
			String sheetname = sheet.getName();
			if(StringUtils.isEmpty(sheetname)) {
				sheetname = fromname;
			}
			HSSFSheet newSheet = wb.createSheet(sheetname);
			copySheet(wb, fromSheet, newSheet, true);
			parseSheet(getContext(), newSheet);
			return wb;
		} catch (Exception e) {
			throw new ExcelException(e.getMessage());
		}
	}
	public static void export(ServletContext ctx, String config,
			ExcelSheet sheet, int sheetindex ,OutputStream out) throws ExcelException {
		try {
			/*HSSFWorkbook wb = WorkbookUtil.openWorkbook(ctx, config);
			HSSFSheet fromSheet = wb.getSheetAt(sheetindex);
			while (wb.getNumberOfSheets() > 0) {
				wb.removeSheetAt(0);
			}
			ExcelUtil.context.set(null);
			addValue("value", sheet.getData());
			HSSFSheet newSheet = wb.createSheet(sheet.getName());
			copySheet(wb, fromSheet, newSheet, true);
			parseSheet(getContext(), newSheet);*/
			HSSFWorkbook wb = createHSSFWorkbook(ctx,config,sheet,sheetindex);
			wb.write(out);
		} catch (Exception e) {
			throw new ExcelException(e.getMessage());
		}
	}
	
	public static void export(ServletContext ctx, String config,
			ExcelSheet sheet, OutputStream out) throws ExcelException {
		export(ctx,config,sheet,0,out);
	}
	@SuppressWarnings("unchecked")
	public static HSSFWorkbook createHSSFWorkbook(ServletContext ctx, String config,
			List<ExcelSheet> sheets,int sheetindex) {
		try {
			HSSFWorkbook wb = WorkbookUtil.openWorkbook(ctx, config);
			HSSFSheet fromSheet = wb.getSheetAt(0);
			String fromname = fromSheet.getSheetName();
			while (wb.getNumberOfSheets() > 0) {
				wb.removeSheetAt(0);
			}
			ExcelUtil.context.set(null);
			for (ExcelSheet sheet : sheets) {
				addValue("value", sheet.getData());
				String sheetname = sheet.getName();
				if(StringUtils.isEmpty(sheetname)) {
					sheetname = fromname;
				}
				HSSFSheet newSheet = wb.createSheet(sheetname);
				copySheet(wb, fromSheet, newSheet, true);
				parseSheet(getContext(), newSheet);
			} 
			/*
			 * Set<Entry<String, Object>> entrys =data.entrySet();
			 * Iterator<Entry<String, Object>> i = entrys.iterator();
			 * while(i.hasNext()){ Entry<String, Object> entry = i.next();
			 * String key = entry.getKey(); Object value = entry.getValue();
			 * addValue("value", value); HSSFSheet newSheet =
			 * wb.createSheet(key); copySheet(wb, fromSheet, newSheet, true);
			 * parseSheet(getContext(), newSheet); }
			 */
			return wb;
		} catch (Exception e) {
			e.printStackTrace();
			throw new ExcelException(e.getMessage());
		}
	}
	public static void export(ServletContext ctx, String config,
			List<ExcelSheet> sheets,int sheetindex, OutputStream out) throws ExcelException {
		try {
			/*HSSFWorkbook wb = WorkbookUtil.openWorkbook(ctx, config);
			HSSFSheet fromSheet = wb.getSheetAt(0);
			while (wb.getNumberOfSheets() > 0) {
				wb.removeSheetAt(0);
			}
			ExcelUtil.context.set(null);
			for (ExcelSheet sheet : sheets) {
				addValue("value", sheet.getData());
				HSSFSheet newSheet = wb.createSheet(sheet.getName());
				copySheet(wb, fromSheet, newSheet, true);
				parseSheet(getContext(), newSheet);
			}*/
			HSSFWorkbook wb = createHSSFWorkbook(ctx,config,sheets,sheetindex);
			/*
			 * Set<Entry<String, Object>> entrys =data.entrySet();
			 * Iterator<Entry<String, Object>> i = entrys.iterator();
			 * while(i.hasNext()){ Entry<String, Object> entry = i.next();
			 * String key = entry.getKey(); Object value = entry.getValue();
			 * addValue("value", value); HSSFSheet newSheet =
			 * wb.createSheet(key); copySheet(wb, fromSheet, newSheet, true);
			 * parseSheet(getContext(), newSheet); }
			 */
			wb.write(out);
		} catch (Exception e) {
			e.printStackTrace();
			throw new ExcelException(e.getMessage());
		}
	}
	public static void export(ServletContext ctx, String config,
			List<ExcelSheet> sheets, OutputStream out) throws ExcelException {
		export(ctx,config,sheets,0,out);
	}
	@SuppressWarnings("unchecked")
	public static HSSFWorkbook createHSSFWorkbook(ServletContext ctx, String config,ExcelSheet sheet,
			List<ExcelSheet> sheets) throws ExcelException {
		try {
			HSSFWorkbook wb = WorkbookUtil.openWorkbook(ctx, config);
			HSSFSheet firstSheet = wb.getSheetAt(0);
			String firstname = firstSheet.getSheetName();
			HSSFSheet secondSheet = wb.getSheetAt(1);
			String secondname = secondSheet.getSheetName();
			while (wb.getNumberOfSheets() > 0) {
				wb.removeSheetAt(0);
			}
			ExcelUtil.context.set(null);
			if(null!=sheet){
				addValue("value", sheet.getData());
				String sheetname = sheet.getName();
				if(StringUtils.isEmpty(sheetname)) {
					sheetname = firstname;
				}
				HSSFSheet newSheet = wb.createSheet(sheetname);
				copySheet(wb, firstSheet, newSheet, true);
				parseSheet(getContext(), newSheet);
			}
			ExcelUtil.context.set(null);
			if(null!=sheets && sheets.size() > 0){
				for (ExcelSheet sheet_ : sheets) {
					addValue("value", sheet_.getData());
					String sheetname = sheet_.getName();
					if(StringUtils.isEmpty(sheetname)) {
						sheetname = secondname;
					}
					HSSFSheet newSheet = wb.createSheet(sheetname);
					copySheet(wb, secondSheet, newSheet, true);
					parseSheet(getContext(), newSheet);
				}
			}
			/*
			 * Set<Entry<String, Object>> entrys =data.entrySet();
			 * Iterator<Entry<String, Object>> i = entrys.iterator();
			 * while(i.hasNext()){ Entry<String, Object> entry = i.next();
			 * String key = entry.getKey(); Object value = entry.getValue();
			 * addValue("value", value); HSSFSheet newSheet =
			 * wb.createSheet(key); copySheet(wb, fromSheet, newSheet, true);
			 * parseSheet(getContext(), newSheet); }
			 */
			return wb;
		} catch (Exception e) {
			e.printStackTrace();
			throw new ExcelException(e.getMessage());
		}
	}
	public static void export(ServletContext ctx, String config,ExcelSheet sheet,
			List<ExcelSheet> sheets, OutputStream out) throws ExcelException {
		try {
			HSSFWorkbook wb = createHSSFWorkbook(ctx,config,sheet,sheets);
			/*HSSFWorkbook wb = WorkbookUtil.openWorkbook(ctx, config);
			HSSFSheet firstSheet = wb.getSheetAt(0);
			HSSFSheet sencondSheet = wb.getSheetAt(1);
			while (wb.getNumberOfSheets() > 0) {
				wb.removeSheetAt(0);
			}
			ExcelUtil.context.set(null);
			if(null!=sheet){
				addValue("value", sheet.getData());
				HSSFSheet newSheet = wb.createSheet(sheet.getName());
				copySheet(wb, firstSheet, newSheet, true);
				parseSheet(getContext(), newSheet);
			}
			ExcelUtil.context.set(null);
			if(null!=sheets && sheets.size() > 0){
				for (ExcelSheet sheet_ : sheets) {
					addValue("value", sheet_.getData());
					HSSFSheet newSheet = wb.createSheet(sheet_.getName());
					copySheet(wb, sencondSheet, newSheet, true);
					parseSheet(getContext(), newSheet);
				}
			}*/
			/*
			 * Set<Entry<String, Object>> entrys =data.entrySet();
			 * Iterator<Entry<String, Object>> i = entrys.iterator();
			 * while(i.hasNext()){ Entry<String, Object> entry = i.next();
			 * String key = entry.getKey(); Object value = entry.getValue();
			 * addValue("value", value); HSSFSheet newSheet =
			 * wb.createSheet(key); copySheet(wb, fromSheet, newSheet, true);
			 * parseSheet(getContext(), newSheet); }
			 */
			wb.write(out);
		} catch (Exception e) {
			e.printStackTrace();
			throw new ExcelException(e.getMessage());
		}
	}
	@SuppressWarnings("unchecked")
	public static HSSFWorkbook createHSSFWorkbook(ServletContext ctx, String config,List<ExcelSheet> sheets1,
			List<ExcelSheet> sheets2) throws ExcelException {
		try {
			HSSFWorkbook wb = WorkbookUtil.openWorkbook(ctx, config);
			HSSFSheet firstSheet = wb.getSheetAt(0);
			String firstname = firstSheet.getSheetName();
			HSSFSheet secondSheet = wb.getSheetAt(1);
			String secondname = secondSheet.getSheetName();
			while (wb.getNumberOfSheets() > 0) {
				wb.removeSheetAt(0);
			}
			ExcelUtil.context.set(null);
			if(null!=sheets1 && sheets1.size() > 0) {
				for (ExcelSheet sheet_ : sheets1) {
					addValue("value", sheet_.getData());
					String sheetname = sheet_.getName();
					if(StringUtils.isEmpty(sheetname)) {
						sheetname = firstname;
					}
					HSSFSheet newSheet = wb.createSheet(sheetname);
					copySheet(wb, firstSheet, newSheet, true);
					parseSheet(getContext(), newSheet);
				}
			}
			ExcelUtil.context.set(null);
			if(null!=sheets2 && sheets2.size() > 0){
				for (ExcelSheet sheet_ : sheets2) {
					addValue("value", sheet_.getData());
					String sheetname = sheet_.getName();
					if(StringUtils.isEmpty(sheetname)) {
						sheetname = secondname;
					}
					HSSFSheet newSheet = wb.createSheet(sheetname);
					//HSSFSheet newSheet = wb.createSheet(sheet_.getName());
					copySheet(wb, secondSheet, newSheet, true);
					parseSheet(getContext(), newSheet);
				}
			}
			/*
			 * Set<Entry<String, Object>> entrys =data.entrySet();
			 * Iterator<Entry<String, Object>> i = entrys.iterator();
			 * while(i.hasNext()){ Entry<String, Object> entry = i.next();
			 * String key = entry.getKey(); Object value = entry.getValue();
			 * addValue("value", value); HSSFSheet newSheet =
			 * wb.createSheet(key); copySheet(wb, fromSheet, newSheet, true);
			 * parseSheet(getContext(), newSheet); }
			 */
			return wb;
		} catch (Exception e) {
			throw new ExcelException(e.getMessage());
		}
	}
	public static void export(ServletContext ctx, String config,List<ExcelSheet> sheets1,
			List<ExcelSheet> sheets2, OutputStream out) throws ExcelException {
		try {
			HSSFWorkbook wb = createHSSFWorkbook(ctx, config,sheets1,sheets2);
			/*HSSFWorkbook wb = WorkbookUtil.openWorkbook(ctx, config);
			HSSFSheet firstSheet = wb.getSheetAt(0);
			HSSFSheet sencondSheet = wb.getSheetAt(1);
			while (wb.getNumberOfSheets() > 0) {
				wb.removeSheetAt(0);
			}
			ExcelUtil.context.set(null);
			if(null!=sheets1 && sheets1.size() > 0) {
				for (ExcelSheet sheet_ : sheets1) {
					addValue("value", sheet_.getData());
					HSSFSheet newSheet = wb.createSheet(sheet_.getName());
					copySheet(wb, firstSheet, newSheet, true);
					parseSheet(getContext(), newSheet);
				}
			}
			ExcelUtil.context.set(null);
			if(null!=sheets2 && sheets2.size() > 0){
				for (ExcelSheet sheet_ : sheets2) {
					addValue("value", sheet_.getData());
					HSSFSheet newSheet = wb.createSheet(sheet_.getName());
					copySheet(wb, sencondSheet, newSheet, true);
					parseSheet(getContext(), newSheet);
				}
			}*/
			/*
			 * Set<Entry<String, Object>> entrys =data.entrySet();
			 * Iterator<Entry<String, Object>> i = entrys.iterator();
			 * while(i.hasNext()){ Entry<String, Object> entry = i.next();
			 * String key = entry.getKey(); Object value = entry.getValue();
			 * addValue("value", value); HSSFSheet newSheet =
			 * wb.createSheet(key); copySheet(wb, fromSheet, newSheet, true);
			 * parseSheet(getContext(), newSheet); }
			 */
			wb.write(out);
		} catch (Exception e) {
			throw new ExcelException(e.getMessage());
		}
	}
	@SuppressWarnings("unchecked")
	public static HSSFWorkbook createHSSFWorkbook(ServletContext ctx, String config, List<ExcelSheet> ...sheets ) {
		try {
			HSSFWorkbook wb = WorkbookUtil.openWorkbook(ctx, config);
			//int size = sheets.length;
			int size = wb.getNumberOfSheets();
			HSSFSheet[] sheet = new HSSFSheet[size];
			String[] sheetnames = new String[size];
			Map<String,HSSFSheet> sheetmap = new HashMap<String,HSSFSheet>();
			for(int i=0;i<size;i++) {
				HSSFSheet sheet_ = wb.getSheetAt(i);
				sheet[i]=sheet_;
				sheetnames[i] = sheet_.getSheetName();
				sheetmap.put(sheet_.getSheetName(), sheet[i]);
			}
			while (wb.getNumberOfSheets() > 0) {
				wb.removeSheetAt(0);
			}
			HSSFSheet tmpsheet = null;
			for(int i=0;i<size;i++) {
				ExcelUtil.context.set(null);
				tmpsheet = null;
				List<ExcelSheet> list = sheets[i];
				for(ExcelSheet sheet_ : list) {
					addValue("value", sheet_.getData());
					String sheetname = sheet_.getName();
					if(StringUtils.isNotEmpty(sheetname)) {
						tmpsheet = sheetmap.get(sheetname);
					}
					if(null==tmpsheet) {
						tmpsheet = sheet[i];
					}
					if(StringUtils.isEmpty(sheetname)) {
						sheetname = sheetnames[i];
					}
					HSSFSheet newSheet = wb.createSheet(sheetname);
					copySheet(wb, tmpsheet, newSheet, true);
					parseSheet(getContext(), newSheet);
				}
			}
			/*
			 * Set<Entry<String, Object>> entrys =data.entrySet();
			 * Iterator<Entry<String, Object>> i = entrys.iterator();
			 * while(i.hasNext()){ Entry<String, Object> entry = i.next();
			 * String key = entry.getKey(); Object value = entry.getValue();
			 * addValue("value", value); HSSFSheet newSheet =
			 * wb.createSheet(key); copySheet(wb, fromSheet, newSheet, true);
			 * parseSheet(getContext(), newSheet); }
			 */
			//wb.write(out);
			return wb;
		} catch (Exception e) {
			throw new ExcelException(e.getMessage());
		}
	}
	@SuppressWarnings("unchecked")
	public static void export(ServletContext ctx, String config, OutputStream out,List<ExcelSheet> ...sheets ) throws ExcelException {
		try {
			HSSFWorkbook wb = createHSSFWorkbook(ctx, config,sheets);
			/*HSSFWorkbook wb = WorkbookUtil.openWorkbook(ctx, config);
			HSSFSheet[] sheet = new HSSFSheet[sheets.length];
			int size = sheets.length;
			for(int i=0;i<size;i++) {
				sheet[i]=wb.getSheetAt(i);
			}
			//HSSFSheet firstSheet = wb.getSheetAt(0);
			//HSSFSheet sencondSheet = wb.getSheetAt(1);
			while (wb.getNumberOfSheets() > 0) {
				wb.removeSheetAt(0);
			}
			for(int i=0;i<size;i++) {
				ExcelUtil.context.set(null);
				List<ExcelSheet> list = sheets[i];
				for(ExcelSheet sheet_ : list) {
					addValue("value", sheet_.getData());
					HSSFSheet newSheet = wb.createSheet(sheet_.getName());
					copySheet(wb, sheet[i], newSheet, true);
					parseSheet(getContext(), newSheet);
				}
			}*/
			/*
			 * Set<Entry<String, Object>> entrys =data.entrySet();
			 * Iterator<Entry<String, Object>> i = entrys.iterator();
			 * while(i.hasNext()){ Entry<String, Object> entry = i.next();
			 * String key = entry.getKey(); Object value = entry.getValue();
			 * addValue("value", value); HSSFSheet newSheet =
			 * wb.createSheet(key); copySheet(wb, fromSheet, newSheet, true);
			 * parseSheet(getContext(), newSheet); }
			 */
			wb.write(out);
		} catch (Exception e) {
			throw new ExcelException(e.getMessage());
		}
	}
	@SuppressWarnings("unchecked")
	public static HSSFWorkbook createHSSFWorkbook(ServletContext ctx, String config, ExcelSheet ...sheets ) {
		try {
			HSSFWorkbook wb = WorkbookUtil.openWorkbook(ctx, config);
			//int size = sheets.length;
			int size = wb.getNumberOfSheets();
			HSSFSheet[] sheet = new HSSFSheet[size];
			String[] sheetnames = new String[size];
			Map<String,HSSFSheet> sheetmap = new HashMap<String,HSSFSheet>();
			for(int i=0;i<size;i++) {
				HSSFSheet sheet_ = wb.getSheetAt(i);
				sheet[i]=sheet_;
				sheetmap.put(sheet_.getSheetName(), sheet_);
				sheetnames[i]=sheet_.getSheetName();
			}
			while (wb.getNumberOfSheets() > 0) {
				wb.removeSheetAt(0);
			}
			HSSFSheet tmpsheet = null;
			for(int i=0;i<size;i++) {
				ExcelUtil.context.set(null);
				tmpsheet = null;
				ExcelSheet sheet_ = sheets[i];
				//for(ExcelSheet sheet_ : list) {
					addValue("value", sheet_.getData());
					String sheetname = sheet_.getName();
					if(StringUtils.isNotEmpty(sheetname)) {
						tmpsheet = sheetmap.get(sheetname);
					}
					if(null==tmpsheet) {
						tmpsheet = sheet[i];
					}
					if(StringUtils.isEmpty(sheetname)) {
						sheetname = sheetnames[i];
					}
					HSSFSheet newSheet = wb.createSheet(sheetname);
					copySheet(wb, tmpsheet, newSheet, true);
					parseSheet(getContext(), newSheet);
				//}
			}
			/*
			 * Set<Entry<String, Object>> entrys =data.entrySet();
			 * Iterator<Entry<String, Object>> i = entrys.iterator();
			 * while(i.hasNext()){ Entry<String, Object> entry = i.next();
			 * String key = entry.getKey(); Object value = entry.getValue();
			 * addValue("value", value); HSSFSheet newSheet =
			 * wb.createSheet(key); copySheet(wb, fromSheet, newSheet, true);
			 * parseSheet(getContext(), newSheet); }
			 */
			//wb.write(out);
			return wb;
		} catch (Exception e) {
			throw new ExcelException(e.getMessage());
		} 
	}
	public static void export(ServletContext ctx, String config, OutputStream out,ExcelSheet ...sheets) throws ExcelException {
		try {
			HSSFWorkbook wb = createHSSFWorkbook(ctx, config,sheets);
			/*HSSFWorkbook wb = WorkbookUtil.openWorkbook(ctx, config);
			HSSFSheet[] sheet = new HSSFSheet[sheets.length];
			int size = sheets.length;
			for(int i=0;i<size;i++) {
				sheet[i]=wb.getSheetAt(i);
			}
			//HSSFSheet firstSheet = wb.getSheetAt(0);
			//HSSFSheet sencondSheet = wb.getSheetAt(1);
			while (wb.getNumberOfSheets() > 0) {
				wb.removeSheetAt(0);
			}
			for(int i=0;i<size;i++) {
				ExcelUtil.context.set(null);
				List<ExcelSheet> list = sheets[i];
				for(ExcelSheet sheet_ : list) {
					addValue("value", sheet_.getData());
					HSSFSheet newSheet = wb.createSheet(sheet_.getName());
					copySheet(wb, sheet[i], newSheet, true);
					parseSheet(getContext(), newSheet);
				}
			}*/
			/*
			 * Set<Entry<String, Object>> entrys =data.entrySet();
			 * Iterator<Entry<String, Object>> i = entrys.iterator();
			 * while(i.hasNext()){ Entry<String, Object> entry = i.next();
			 * String key = entry.getKey(); Object value = entry.getValue();
			 * addValue("value", value); HSSFSheet newSheet =
			 * wb.createSheet(key); copySheet(wb, fromSheet, newSheet, true);
			 * parseSheet(getContext(), newSheet); }
			 */
			wb.write(out);
		} catch (Exception e) {
			throw new ExcelException(e.getMessage());
		}
	}
  /**
   * parse the excel template in a sheet and output excel to outputStream in
   * default context.
   * 
   * @param ctx
   * @param config
   * @param sheetIndex
   * @param out
   * @throws ExcelException
   */
  public static void export(ServletContext ctx, String config, int sheetIndex, OutputStream out) throws ExcelException {
    try {
      export(ctx, config, sheetIndex, getContext(), out);
    } catch (Exception e) {
      throw new ExcelException(e.getMessage());
    }
  }

  public static HSSFWorkbook createHSSFWorkbook(String fileName, Object context) throws ExcelException {
	 try {
	      HSSFWorkbook wb = WorkbookUtil.openWorkbook(fileName);
	      parseWorkbook(context, wb);
	      // wb.write(out);
	      return wb;
	  } catch (Exception e) {
	     throw new ExcelException(e.getMessage());
	  }
  }
  /**
   * parse excel and export
   * 
   * @param fileName
   * @param context
   * @param out
   * @throws ExcelException
   */
  public static void export(String fileName, Object context, OutputStream out) throws ExcelException {
    try {
      HSSFWorkbook wb = createHSSFWorkbook(fileName,context);
      /*HSSFWorkbook wb = WorkbookUtil.openWorkbook(fileName);
      parseWorkbook(context, wb);*/
      wb.write(out);
    } catch (Exception e) {
      throw new ExcelException(e.getMessage());
    }
  }

  public static HSSFWorkbook createHSSFWorkbook(String fileName, int sheetIndex, Object context) throws ExcelException {
	 try {
		HSSFWorkbook wb = WorkbookUtil.openWorkbook(fileName);
		parseWorkbook(context, wb, sheetIndex);
		// wb.write(out);
		return wb;
	 } catch (Exception e) {
		throw new ExcelException(e.getMessage());
	 }
  }
  /**
   * parse exel and export
   * 
   * @param fileName
   * @param sheetIndex
   * @param out
   * @throws ExcelException
   */
  public static void export(String fileName, int sheetIndex, Object context, OutputStream out) throws ExcelException {
    try {
      /*HSSFWorkbook wb = WorkbookUtil.openWorkbook(fileName);
      parseWorkbook(context, wb, sheetIndex);*/
      HSSFWorkbook wb = createHSSFWorkbook(fileName,sheetIndex,context);
      wb.write(out);
    } catch (Exception e) {
      throw new ExcelException(e.getMessage());
    }
  }

  /**
   * parse excel and export excel
   * 
   * @param fileName
   * @param out
   * @throws ExcelException
   */
  public static void export(String fileName, OutputStream out) throws ExcelException {
    try {
      export(fileName, getContext(), out);
    } catch (Exception e) {
      throw new ExcelException(e.getMessage());
    }
  }

  /**
   * parse excel and export excel
   * 
   * @param fileName
   * @param sheetIndex
   * @param out
   * @throws ExcelException
   */
  public static void export(String fileName, int sheetIndex, OutputStream out) throws ExcelException {
    try {
      export(fileName, sheetIndex, getContext(), out);
    } catch (Exception e) {
      throw new ExcelException(e.getMessage());
    }
  }

  /**
   * @param inputStream
   * @param context
   * @param out
   * @throws ExcelException
   */
  public static void export(InputStream inputStream, Object context, OutputStream out) throws ExcelException {
    try {
      HSSFWorkbook wb = WorkbookUtil.openWorkbook(inputStream);
      parseWorkbook(context, wb);
      wb.write(out);
    } catch (Exception e) {
      throw new ExcelException(e.getMessage());
    }
  }

  /**
   * parse workbook
   * 
   * @param context
   * @param wb
   * @throws ExcelException
   */
  public static void parseWorkbook(Object context, HSSFWorkbook wb) throws ExcelException {
    try {
      int sheetCount = wb.getNumberOfSheets();
      for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
        HSSFSheet sheet = wb.getSheetAt(sheetIndex);
        parseSheet(context, sheet);
      }
    } catch (Exception e) {
      throw new ExcelException(e.getMessage());
    }
  }

  /**
   * parse Workbook
   * 
   * @param context
   * @param wb
   * @param sheetIndex
   * @throws ExcelException
   */
  public static void parseWorkbook(Object context, HSSFWorkbook wb, int sheetIndex) throws ExcelException {
    try {
      HSSFSheet sheet = wb.getSheetAt(sheetIndex);
      if (null != sheet) {
        parseSheet(context, sheet);
      }

      int i = 0;
      while (i++ < sheetIndex) {
        wb.removeSheetAt(0);
      }

      i = 1;
      while (i < wb.getNumberOfSheets()) {
        wb.removeSheetAt(i);
      }
    } catch (Exception e) {
      throw new ExcelException(e.getMessage());
    }
  }

  /**
   * parse Excel Template File
   * @param context datasource
   * @param sheet Workbook sheet
   */
  @SuppressWarnings("unchecked")
  public static void parseSheet(Object context, HSSFSheet sheet) throws ExcelException {
    try {
      ExcelParser.parse(context, sheet, sheet.getFirstRowNum(), sheet.getLastRowNum());
    } catch (Exception e) {
      e.printStackTrace();
      throw new ExcelException(e.getMessage());
    } finally {
      ExcelUtil.context.set(null);
    }
  }

  public static void addService(Object context, String key, Object service) {
    addValue(context, key, service);
  }

  public static void addService(String key, Object service) {
    addValue(key, service);
  }

  /**
   * add a object to context
   * @param context must be a DynaBean or Map type
   * @param value
   */
  @SuppressWarnings({ "unchecked", "rawtypes" })
  public static void addValue(Object context, String key, Object value) {
    if (context instanceof DynaBean) {
      ((DynaBean) context).set(key, value);
    } else if (context instanceof Map) {
      ((Map) context).put(key, value);
    }
  }

  /**
   * add a object to default context
   * 
   * @param key
   * @param value
   */
  public static void addValue(String key, Object value) {
    getContext().set(key, value);
  }

  /**
   * register extended tag package, default is net.sf.excelutils.tags
   * 
   * @param packageName
   */
  @SuppressWarnings("unchecked")
  public synchronized static void registerTagPackage(String packageName) {
    ExcelParser.tagPackageMap.put(packageName, packageName);
  }

  /**
   * get a global context, it's thread safe
   * 
   * @return DynaBean
   */
  public static DynaBean getContext() {
    DynaBean ctx = (DynaBean) context.get();
    if (null == ctx) {
      ctx = new LazyDynaBean();
      setContext(ctx);
    }
    return ctx;
  }

  /**
   * set global context
   * 
   * @param ctx DynaBean
   */
  @SuppressWarnings("unchecked")
  public static void setContext(DynaBean ctx) {
    context.set(ctx);
  }
  
  /**
   * can value be show
   * @param value
   * @return boolean
   */
  public static boolean isCanShowType(Object value) {
  	if (null == value) return false;
  	String valueType = value.getClass().getName();
    return "java.lang.String".equals(valueType) || "java.lang.Double".equals(valueType)
        || "java.lang.Integer".equals(valueType) || "java.lang.Boolean".equals(valueType)
        || "java.sql.Timestamp".equals(valueType) || "java.util.Date".equals(valueType)
        || "java.lang.Byte".equals(valueType) || "java.math.BigDecimal".equals(valueType)
        || "java.math.BigInteger".equals(valueType) || "java.lang.Float".equals(valueType)
        || value.getClass().isPrimitive();
  }
  /////////////////////////////////////////////////////////////////////////////
  //easy excel start
	/**
     * 读取Excel文件，可能是03也可能是07版本
     */
    public static void readExcel(final IExcelRowReader reader, final String excelFilePath) throws Exception {
    	if(null==reader) {
    		throw new ExcelException("IExcelRowReader不能为空。");
    	}
    	if(StringUtils.isEmpty(excelFilePath)) {
    		throw new ExcelException("文件路径不能为空。");
    	}
    	// 处理excel2003文件
        if (excelFilePath.endsWith(".xls")) {
            ExcelXlsReader exceXls = new ExcelXlsReader();
            exceXls.setRowReader(reader); 
            exceXls.process(excelFilePath);
            // 处理excel2007文件
        } else if (excelFilePath.endsWith(".xlsx")) {
            ExcelXlsxReader exceXlsx = new ExcelXlsxReader();
            exceXlsx.setRowReader(reader); 
            exceXlsx.process(excelFilePath);
        } else {
            throw new ExcelException("文件格式错误，扩展名只能是xls或xlsx。");
        }
    }
    /**
     * 
     * @param reader
     * @param is
     * @param fileName
     */
    public static void readExcel(final IExcelRowReader reader, InputStream is,final String fileName)  throws Exception {
    	if(null==reader) {
    		throw new ExcelException("IExcelRowReader不能为空。");
    	}
    	if(null==is) {
    		throw new ExcelException("文件内容不能为空。");
    	}
    	if(StringUtils.isEmpty(fileName)) {
    		throw new ExcelException("文件名不能为空。");
    	}// 处理excel2003文件
        if (fileName.endsWith(".xls")) {
            ExcelXlsReader exceXls = new ExcelXlsReader();
            exceXls.setRowReader(reader); 
            exceXls.process(is);
            // 处理excel2007文件
        } else if (fileName.endsWith(".xlsx")) {
            ExcelXlsxReader exceXlsx = new ExcelXlsxReader();
            exceXlsx.setRowReader(reader); 
            exceXlsx.process(is);
        } else {
            throw new ExcelException("文件格式错误，fileName的扩展名只能是xls或xlsx。");
        }
    }
	//easy excel end
    /////////////////////////////////////////////////////////////////////////////
}